﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace 健康管理系统
{
    public partial class 亚健康人群 : Form
    {
        private DataTable userTable;
        private DataView CurrentPageView;
        private int PageSize;
        private int CurrentPageNo;
        private int MaxPageNo;
        
        public 亚健康人群()
        {
            InitializeComponent();
            this.StartPosition = FormStartPosition.CenterScreen;                                            //本窗体启动位置设为屏幕中央； 
            this.dgv_user.AllowUserToAddRows = false;                                                      //数据网格视图不允许用户添加行；
            this.dgv_user.RowHeadersVisible = false;                                                       //数据网格视图的行标题不可见；
            this.dgv_user.BackgroundColor = Color.White;                                                   //数据网格视图的背景色设为白色；
            this.dgv_user.AutoSizeColumnsMode =
                DataGridViewAutoSizeColumnsMode.AllCells;    
        }

        private void btn_fanhui_Click(object sender, EventArgs e)
        {
            this.Hide();
            主页面 m = new 主页面();
            m.Show();
        }

        private void btn_tianjia_Click(object sender, EventArgs e)
        {
            this.Hide();
            添加亚健康 m = new 添加亚健康();
            m.Show();
        }

        private void dgv_user_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
             
        }

        private void btn_chaxun_Click(object sender, EventArgs e)
        {
            if (comboBox1.Text== "病历号")
            {
                //DataTable userTable = new DataTable();
                DataRow searchResultRow = this.userTable.Rows.Find(this.textBox2.Text.Trim());            //借助本窗体的课程数据表的行集合的方法Find，根据主键值（即课程编号）快速查找相应课程，并返回其所在的数据行；但数据行不能作为数据源，需另行创建数据表，并导入该数据行，最后将数据表作为数据源；
                DataTable searchResultTable = this.userTable.Clone();                                         //借助本窗体的课程数据表的方法Clone，创建相同架构的空表，用于保存搜索结果所在数据行；
                searchResultTable.ImportRow(searchResultRow);                                                   //将（复制后的）数据行导入数据表；
                this.dgv_user.DataSource = searchResultTable;                                                 //将数据网格视图的数据源设为搜索结果数据表；
            }
            else
            {
                if (comboBox1.Text== "姓名")
                {
                    DataRow[] searchResultRows =
                    this.userTable.Select("u_name LIKE '%" + this.textBox2.Text.Trim() + "%'");             //借助本窗体的课程数据表的方法Select，并提供与SQL类似的谓词表达式作为查询条件，根据拼音缩写进行模糊查询（仅支持%通配符）；查询将返回数据行数组；
                    DataTable searchResultTable = this.userTable.Clone();                                         //借助本窗体的课程数据表的方法Clone，创建相同架构的空表，用于保存搜索结果所在数据行；
                    foreach (DataRow row in searchResultRows)                                                       //遍历搜索结果所在数据行数组；
                    {
                        searchResultTable.ImportRow(row);                                                           //数据行导入数据表；
                    }
                    this.dgv_user.DataSource = searchResultTable;                                                 //将数据网格视图的数据源设为搜索结果数据表；
                }
            }
        }

        private void btn_load_Click(object sender, EventArgs e)
        {
            
                                                                 
        }

        private void btn_update_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
               "Server='DXL-D55E6339D08';Database='HealthManagementSystem';Integrated Security=sspi";
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText =
                "UPDATE tb_user"
                + " SET u_name=@Name,u_sex=@Gender,u_age=@Age,u_address=@Address,u_phone=@Phone,u_doctor=@Doctor,u_date=@Date,u_result=@Result,u_signs=@Signs,u_mehistory=@Mehistory"
                + " WHERE u_no=@No;";
            sqlCommand.Parameters.Add("@Name",SqlDbType.VarChar,0,"u_name");
            sqlCommand.Parameters.Add("@Gender",SqlDbType.Bit,0,"u_sex");
            sqlCommand.Parameters.Add("@Age",SqlDbType.VarChar,0,"u_age" );
            sqlCommand.Parameters.Add("@Address",SqlDbType.VarChar,0,"u_address");
            sqlCommand.Parameters.Add("@Phone",SqlDbType.VarChar,0,"u_phone" );
            sqlCommand.Parameters.Add("@Doctor",SqlDbType.Int,0,"u_doctor" );
            sqlCommand.Parameters.Add("@Date",SqlDbType.Date,0,"u_date" );
            sqlCommand.Parameters.Add("@Result",SqlDbType.VarChar,0,"u_result");
            sqlCommand.Parameters.Add("@Signs", SqlDbType.VarChar,0,"u_signs");
            sqlCommand.Parameters.Add("@Mehistory",SqlDbType.VarChar,0,"u_mehistory");
            sqlCommand.Parameters.Add("@No", SqlDbType.Char, 0, "u_no");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.UpdateCommand = sqlCommand;
            //this.dgv_user.DataSource = this.userTable;
            DataTable userTable1 =(DataTable)this.dgv_user.DataSource;
            sqlConnection.Open();
            int rowAffected = sqlDataAdapter.Update(userTable1);
            sqlConnection.Close();
            MessageBox.Show("修改" + rowAffected.ToString() + "行。");     
        }

        private void 亚健康人群_Load(object sender, EventArgs e)
        {
            this.PageSize = 10;
            this.CurrentPageNo = 1;
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
                 "Server='DXL-D55E6339D08';Database='HealthManagementSystem';Integrated Security=sspi";
            SqlCommand sqlCommand = new SqlCommand();
            SqlCommand sqlCommand2 = new SqlCommand();
            SqlCommand sqlCommand3 = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand2.Connection = sqlConnection;
            sqlCommand3.Connection = sqlConnection;
            sqlCommand.CommandText = "SELECT * FROM tb_doctor;";
            //sqlCommand2.CommandText = "SELECT * FROM tb_user WHERE 1=0;";
            sqlCommand2.CommandText = "SELECT * FROM tb_user;";
            sqlCommand3.CommandText = "SELECT * FROM tb_gender;";
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataTable doctorTable = new DataTable();
            SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter();
            sqlDataAdapter2.SelectCommand = sqlCommand2;
            sqlDataAdapter2.MissingSchemaAction = MissingSchemaAction.AddWithKey;
             this.userTable = new DataTable();
            //DateTable userTable.TableName = "Usertable";
            SqlDataAdapter sqlDataAdapter3 = new SqlDataAdapter();
            sqlDataAdapter3.SelectCommand = sqlCommand3;
            sqlDataAdapter3.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataTable genderTable = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter.Fill(doctorTable);
            sqlDataAdapter2.Fill(this.userTable);
            sqlDataAdapter3.Fill(genderTable);
            DataColumn rowIdColumn = new DataColumn();
            rowIdColumn.ColumnName = "RowID";
            rowIdColumn.DataType=typeof (int);
            rowIdColumn.AutoIncrement = true;
            rowIdColumn.AutoIncrementSeed = 1;
            rowIdColumn.AutoIncrementStep = 1;
            userTable.Columns.Add(rowIdColumn);
            sqlCommand2.CommandText = "SELECT * FROM tb_user;";
            sqlDataAdapter2.Fill(userTable);
            sqlConnection.Close();
            this.MaxPageNo = userTable.Rows.Count / this.PageSize + 1;
            this.CurrentPageView = new DataView();
            this.CurrentPageView.Table =userTable;
            this.CurrentPageView.Sort = "RowID ASC";
            this.CurrentPageView.RowFilter = "RowID >" + (this.CurrentPageNo - 1) * this.PageSize + " AND RowID<=" + this.CurrentPageNo * this.PageSize;
            this.dgv_user.Columns.Clear();
            this.dgv_user.DataSource = this.CurrentPageView;
            this.dgv_user.Columns["u_no"].HeaderText = "病历号";
            this.dgv_user.Columns["u_name"].HeaderText = "姓名";
            this.dgv_user.Columns["u_age"].HeaderText = "年龄";
            this.dgv_user.Columns["u_address"].HeaderText = "地址";
            this.dgv_user.Columns["u_phone"].HeaderText = "联系电话";
            this.dgv_user.Columns["u_date"].HeaderText = "日期";
            this.dgv_user.Columns["u_result"].HeaderText = "检测结果";
            this.dgv_user.Columns["u_signs"].HeaderText = "体质型";
            this.dgv_user.Columns["u_mehistory"].HeaderText = "病史";
            this.dgv_user.Columns["u_doctor"].Visible = false;
            this.dgv_user.Columns["u_sex"].Visible = false;
            this.dgv_user.Columns["RowID"].Visible = false;
            this.dgv_user.Columns[this.dgv_user.Columns.Count - 1].AutoSizeMode =
                DataGridViewAutoSizeColumnMode.Fill;
            DataGridViewComboBoxColumn doctorColumn = new DataGridViewComboBoxColumn();
            doctorColumn.Name = "doctor";
            doctorColumn.HeaderText = "医师";
            doctorColumn.DataSource = doctorTable;
            doctorColumn.DisplayMember = "d_rename";
            doctorColumn.ValueMember = "d_no";
            doctorColumn.DataPropertyName = "u_doctor";
            doctorColumn.DisplayIndex = 6;
            doctorColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            this.dgv_user.Columns.Add(doctorColumn);
               DataGridViewComboBoxColumn genderColumn = new DataGridViewComboBoxColumn();
            genderColumn.Name = "gender";
            genderColumn.HeaderText = "性别";
            genderColumn.DataSource = genderTable;
            genderColumn.DisplayMember = "g_name";
            genderColumn.ValueMember = "g_no";
            genderColumn.DataPropertyName = "u_sex";
            genderColumn.DisplayIndex = 3;
            genderColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            this.dgv_user.Columns.Add(genderColumn);
               
        }

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            if (this.CurrentPageNo > 1)
            {
                this.CurrentPageNo--;
            }
            this.CurrentPageView.RowFilter = "RowID >" + (this.CurrentPageNo - 1) * this.PageSize
                + " AND RowID<=" + this.CurrentPageNo * this.PageSize;
        }

        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            if (this.CurrentPageNo <this .MaxPageNo)
            {
                this.CurrentPageNo++;
            }
            this.CurrentPageView.RowFilter = "RowID >" + (this.CurrentPageNo - 1) * this.PageSize
                + " AND RowID<=" + this.CurrentPageNo * this.PageSize;
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {
        
        }
      }
    }

